import pymysql
import xlwt
import datetime
# 数据库操作
config = {
    "host": "1.117.167.225",
    "user": "root",
    "password": "Aa1234zxcv",
    "database": "qc_live",
    "charset": "utf8"
}


# 查询当日总充值金额
def xiaolu_total_chongzhi(time):
    conn = pymysql.connect(**config)
    dt = datetime.datetime.strptime(time, "%Y-%m-%d")
    time1 = (dt + datetime.timedelta(days=1)).strftime("%Y-%m-%d")
    cursor = conn.cursor()
    query = 'select sum(coin) as coin from t_wallet_recharge_record where is_pay = 1 and order_time >= "' + str(
        time) + '" and order_time<"' + str(time1) + '"'
    cursor.execute(query)
    result = cursor.fetchall()
    total_coin = 0
    for (item0) in result:
        total_coin = item0[0]
    cursor.close()
    conn.close()
    return total_coin


# 查询表里所有数据
def xiaolu_recharge_task(time):
    conn = pymysql.connect(**config)
    # time = '2023-02-12'
    # time1 = '2023-02-13'

    # time = datetime.datetime.now().strftime('%Y-%m-%d')
    # time1 = (datetime.datetime.now() + datetime.timedelta(days=1)).strftime('%Y-%m-%d')
    dt = datetime.datetime.strptime(time, "%Y-%m-%d")
    time1 = (dt + datetime.timedelta(days=1)).strftime("%Y-%m-%d")

    cursor = conn.cursor()
    tab_list = []
    list_user = []

    query = 'select id,uid,nick,sum(coin) as coin from t_wallet_recharge_record where is_pay = 1 and order_time >= "'+str(time)+'" and order_time<"'+str(time1)+'" GROUP BY uid ORDER BY coin DESC'
    cursor.execute(query)
    result = cursor.fetchall()
    for (item0) in result:
        item = [0, item0[1], item0[2]]
        list_user.append(item)

    query = 'select id,oid,sum(coin) as coin from t_wallet_coin_exchange_record where time >= "' + str(
        time) + '" and time<"' + str(time1) + '" GROUP BY uid ORDER BY coin DESC'
    cursor.execute(query)
    result = cursor.fetchall()
    for (item0) in result:
        item = [0, item0[1], '-']
        is_have = False
        for kk in list_user:
            if kk[1] == item0[1]:
                is_have = True
        if not is_have:
            list_user.append(item)

    for (item) in list_user:
        my_map = {'uid': item[1]}
        my_map['nick'] = item[2]
        if item[1] == None:
            continue
        # 查询用户兑换金额
        query1 = 'select sum(coin) from t_wallet_coin_exchange_record where oid=' + str(
            item[1]) + ' and time >= "' + str(
            time) + '" and time<"' + str(time1) + '"'
        cursor.execute(query1)
        result1 = cursor.fetchall()
        exchange_coin = 0
        for (item1) in result1:
            if item1[0] != None:
                exchange_coin = item1[0]
        my_map['exchange'] = exchange_coin

        # 查询用户充值金额
        query1 = 'select sum(coin) from t_wallet_recharge_record where uid='+str(item[1])+' and is_pay = 1 and order_time >= "' + str(
            time) + '" and order_time<"' + str(time1) + '"'
        cursor.execute(query1)
        result1 = cursor.fetchall()
        recharge_coin = 0
        for (item1) in result1:
            if item1[0] != None:
                recharge_coin = item1[0]
        my_map['recharge'] = recharge_coin

        # 查询幸运宝典
        query2 = 'select * from t_game_divination_user_statistics where uid = '+str(item[1])+' and time >= "' + str(time) + '" and time<"' + str(
            time1) + '"'
        cursor.execute(query2)
        result2 = cursor.fetchall()
        book_i = 0
        book_out = 0
        for (item2) in result2:
            # print(item)
            book_i += item2[2]
            book_out += item2[3]
        my_map['input'] = book_i
        my_map['output'] = book_out

        # 查询彩蛋
        query7 = 'select * from t_easter_egg_user_statistics where uid = '+str(item[1])+' and time >= "' + str(time) + '" and time<"' + str(
            time1) + '"'
        cursor.execute(query7)
        result7 = cursor.fetchall()
        egg_i = 0
        egg_out = 0
        for (item7) in result7:
            egg_i += item7[2]
            egg_out += item7[3]
        my_map['input_egg'] = egg_i
        my_map['output_egg'] = egg_out

        # 幸运礼物投入
        query3 = ('select * from t_lucky_gift_statistics where uid = '+str(item[1])+' and time >= "' + str(time) + '" and time<"' + str(
            time1) + '"')
        cursor.execute(query3)
        result3 = cursor.fetchall()
        gift_i = 0
        gift_out = 0
        for (item3) in result3:
            # print(item)
            gift_i = item3[2]
            gift_out = item3[3]
        my_map['inputgift'] = gift_i
        my_map['outputgift'] = gift_out

        # 查询背包余额
        query2 = 'select SUM(num*coin) from t_user_pack as a inner join t_gift_config as b on a.prop_id=b.id where a.uid = ' + str(
            item[1]) + ' and a.time >= "' + str(time) + '" and a.time<' + str(time1) + ' and a.num>0 and a.type =5'
        cursor.execute(query2)
        result2 = cursor.fetchall()
        bg_coin = 0
        for (item2) in result2:
            try:
                bg_coin += item2[0]
            except:
                print('没有背包')
        my_map['bg_coin'] = bg_coin

        # 查询用户账户余额
        query3 = 'select * from t_wallet_user_account where uid = ' + str(item[1])
        cursor.execute(query3)
        result3 = cursor.fetchall()
        coin_s = 0
        cost_s = 0
        for (item3) in result3:
            coin_s = item3[1]
            cost_s = item3[2]
        my_map['coin'] = coin_s
        my_map['cost'] = cost_s

        # 直播间的贡献值分类 直送 背包 type =1 直送  2 背包
        # select * from t_gift_user_statistics where room_id = 104760 and time >= '2023-02-02' order by coin desc
        query4 = ('select * from t_gift_user_statistics where uid = ' + str(item[1]) + ' and time >= "' + str(
            time) + '" and time < "' + str(time1) + '" order by coin desc')
        cursor.execute(query4)
        result4 = cursor.fetchall()
        gift_coin = 0
        bg_coin = 0
        for (item4) in result4:
            if item4[4] == 1:
                gift_coin += item4[3]
            else:
                bg_coin += item4[3]
        my_map['gift'] = gift_coin
        my_map['bggift'] = bg_coin

        # 查询当前直播间所有上榜的用户 上榜人数金额
        # select * from t_live_user_statistics where room_id = 102871 and time >= '2023-02-02'
        query5 = ('select * from t_live_user_statistics where uid = ' + str(item[1]) + ' and time >= "' + str(
            time) + '" and time < "' + str(time1) + '"')
        cursor.execute(query5)
        result5 = cursor.fetchall()
        gx_list = ''
        for (item5) in result5:
            # print('贡献的用户：'+item1[2] + ' 贡献值：'+item1[3])
            gx_list += str(item5[1]) + ' 贡献值：' + str(item5[3]) + '\n '
        my_map['gx_list'] = gx_list

        tab_list.append(my_map)
    cursor.close()

    # 生成报表 input
    work_book = xlwt.Workbook(encoding='utf-8')
    sheet = work_book.add_sheet('sheet表名')
    sheet.write(0, 0, '用户ID')
    sheet.write(0, 1, '昵称')

    sheet.write(0, 3, '宝典投入(元)')
    sheet.write(0, 4, '宝典产出(元)')
    sheet.write(0, 5, '宝典盈亏(元)')

    sheet.write(0, 6, '彩蛋投入(元)')
    sheet.write(0, 7, '彩蛋产出(元)')
    sheet.write(0, 8, '彩蛋盈亏(元)')

    sheet.write(0, 9, '幸运礼物投入')
    sheet.write(0, 10, '幸运礼物产出')
    sheet.write(0, 11, '幸运礼物成本(元)')
    sheet.write(0, 12, '幸运礼物上榜(元)')
    sheet.write(0, 13, '幸运礼物盈亏(元)')

    sheet.write(0, 15, '背包余额(元)')
    sheet.write(0, 16, '鹿角(元)')
    sheet.write(0, 17, '鹿茸(元)')

    sheet.write(0, 19, '用户充值金额(元)')
    sheet.write(0, 20, '用户兑换金额(元)')
    sheet.write(0, 21, '直送上榜(元)')
    sheet.write(0, 22, '背包上榜(元)')
    sheet.write(0, 23, '送礼直播间')
    for index in range(len(tab_list)):
        sheet.write(index + 1, 0, tab_list[index]['uid'])
        sheet.write(index + 1, 1, tab_list[index]['nick'])
        sheet.write(index + 1, 3, tab_list[index]['input']/1000)
        sheet.write(index + 1, 4, tab_list[index]['output']/1000)
        sheet.write(index + 1, 5, (tab_list[index]['output']-tab_list[index]['input'])/1000)
        sheet.write(index + 1, 6, tab_list[index]['input_egg']/1000)
        sheet.write(index + 1, 7, tab_list[index]['output_egg']/1000)
        sheet.write(index + 1, 8, (tab_list[index]['output_egg']-tab_list[index]['input_egg'])/1000)

        sheet.write(index + 1, 9, tab_list[index]['inputgift'])
        sheet.write(index + 1, 10, tab_list[index]['outputgift'])
        sheet.write(index + 1, 11, (tab_list[index]['inputgift'] - tab_list[index]['outputgift']) / 1000)
        sheet.write(index + 1, 12, tab_list[index]['inputgift'] / 10000)
        sheet.write(index + 1, 13, tab_list[index]['inputgift'] / 10000 - (
                    (tab_list[index]['inputgift'] - tab_list[index]['outputgift']) / 1000))

        sheet.write(index + 1, 15, tab_list[index]['bg_coin']/1000)
        sheet.write(index + 1, 16, tab_list[index]['coin']/1000)
        sheet.write(index + 1, 17, tab_list[index]['cost']/1000)

        sheet.write(index + 1, 19, tab_list[index]['recharge']/1000)
        sheet.write(index + 1, 20, tab_list[index]['exchange']/1000)
        sheet.write(index + 1, 21, tab_list[index]['gift']/1000)
        sheet.write(index + 1, 22, tab_list[index]['bggift']/1000)
        sheet.write(index + 1, 23, tab_list[index]['gx_list'])
    work_book.save('C:\\Users\\Administrator\\Documents\\xiaolu\\'+str(time)+'_用户充值行为分析.xls')
    conn.close()


if __name__ == '__main__':
    # 所有充值用户的消费去向，每个游戏的盈亏 给几个直播间上榜了，上榜类型直送1 背包2 幸运
    # datetime.datetime.now().strftime('%Y-%m-%d')
    time = datetime.datetime.now().strftime('%Y-%m-%d')
    time = '2023-02-14'
    xiaolu_recharge_task(time)
